Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# SELECT

Purpose

Use a `SELECT` statement or subquery to retrieve data from one or more tables, object tables, views, object views, or materialized views.

If part or all of the result of a `SELECT` statement is equivalent to an existing materialized view, then Oracle Database may use the materialized view in place of one or more tables specified in the `SELECT` statement. This substitution is called query rewrite. It takes place only if cost optimization is enabled and the `QUERY_REWRITE_ENABLED` parameter is set to `TRUE`. To determine whether query write has occurred, use the `EXPLAIN` `PLAN` statement.

See Also: Chapter 9, "SQL Queries and Subqueries" for general information on queries and subqueries Oracle Data Warehousing Guide for more information on materialized views and query rewrite EXPLAIN PLAN

Additional Topics

- Prerequisites

- Syntax

- Semantics

- Examples

Prerequisites

For you to select data from a table or materialized view, the table or materialized view must be in your own schema or you must have the `SELECT` privilege on the table or materialized view.

For you to select rows from the base tables of a view:

- You must have the `SELECT` privilege on the view, and

- Whoever owns the schema containing the view must have the `SELECT` privilege on the base tables.

The `SELECT` `ANY` `TABLE` system privilege also allows you to select data from any table or any materialized view or the base table of any view.

To issue an Oracle Flashback Query using the `flashback_query_clause`, you must have the `SELECT` privilege on the objects in the select list. In addition, either you must have `FLASHBACK` object privilege on the objects in the select list, or you must have `FLASHBACK` `ANY` `TABLE` system privilege.

Syntax

select::=

Description of the illustration select.gif

(for_update_clause ::=)

subquery::=

Description of the illustration subquery.gif

(subquery_factoring_clause ::=, select_list::=, table_reference::=, hierarchical_query_clause ::=, group_by_clause ::=, model_clause ::=, order_by_clause ::=)

subquery_factoring_clause ::=

Description of the illustration subquery_factoring_clause.gif

select_list::=

Description of the illustration select_list.gif

table_reference::=

Description of the illustration table_reference.gif

(query_table_expression::=, flashback_query_clause ::=)

flashback_query_clause ::=

Description of the illustration flashback_query_clause.gif

query_table_expression::=

Description of the illustration query_table_expression.gif

(subquery_restriction_clause::=, table_collection_expression ::=)

sample_clause ::=

Description of the illustration sample_clause.gif

subquery_restriction_clause::=

Description of the illustration subquery_restriction_clause.gif

table_collection_expression ::=

Description of the illustration table_collection_expression.gif

join_clause ::=

Description of the illustration join_clause.gif

inner_cross_join_clause::=

Description of the illustration inner_cross_join_clause.gif

(table_reference::=, query_partition_clause::=)

outer_join_clause::=

Description of the illustration outer_join_clause.gif

(table_reference::=, query_partition_clause::=)

query_partition_clause::=

Description of the illustration query_partition_clause.gif

outer_join_type::=

Description of the illustration outer_join_type.gif

where_clause::=

Description of the illustration where_clause.gif

hierarchical_query_clause ::=

Description of the illustration hierarchical_query_clause.gif

group_by_clause ::=

Description of the illustration group_by_clause.gif

(rollup_cube_clause::=, grouping_sets_clause::=)

rollup_cube_clause::=

Description of the illustration rollup_cube_clause.gif

(grouping_expression_list::=)

grouping_sets_clause::=

Description of the illustration grouping_sets_clause.gif

(rollup_cube_clause::=, grouping_expression_list::=)

grouping_expression_list::=

Description of the illustration grouping_expression_list.gif

expression_list::=

Description of the illustration expression_list.gif

model_clause ::=

Description of the illustration model_clause.gif

(cell_reference_options::=, return_rows_clause::=, reference_model::=, main_model::=)

cell_reference_options::=

Description of the illustration cell_reference_options.gif

return_rows_clause::=

Description of the illustration return_rows_clause.gif

reference_model::=

Description of the illustration reference_model.gif

(model_column_clauses::=, cell_reference_options::=)

main_model::=

Description of the illustration main_model.gif

(model_column_clauses::=, cell_reference_options::=, model_rules_clause::=)

model_column_clauses::=

Description of the illustration model_column_clauses.gif

(query_partition_clause::=, model_column::=)

model_column::=

Description of the illustration model_column.gif

model_rules_clause::=

Description of the illustration model_rules_clause.gif

(cell_assignment::=, order_by_clause ::=)

cell_assignment::=

Description of the illustration cell_assignment.gif

(single_column_for_loop::=, multi_column_for_loop::=)

single_column_for_loop::=

Description of the illustration single_column_for_loop.gif

multi_column_for_loop::=

Description of the illustration multi_column_for_loop.gif

order_by_clause ::=

Description of the illustration order_by_clause.gif

for_update_clause ::=

Description of the illustration for_update_clause.gif

Semantics

subquery_factoring_clause

The `WITH` `query_name` clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table.

You can specify this clause in any top-level `SELECT` statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.

Restrictions on Subquery Factoring This clause is subject to the following restrictions:

- You can specify only one `subquery_factoring_clause` in a single SQL statement. You cannot specify a `query_name` in its own subquery. However, any `query_name` defined in the `subquery_factoring_clause` can be used in any subsequent named query block in the `subquery_factoring_clause`.

- In a compound query with set operators, you cannot use the `query_name` for any of the component queries, but you can use the `query_name` in the `FROM` clause of any of the component queries.

See Also: Oracle Database Concepts for information about inline views Oracle Data Warehousing Guide and Oracle Database Application Developer's Guide - Fundamentals for information on using the subquery factoring feature "Subquery Factoring: Example"

hint

Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.

See Also: "Using Hints" and Oracle Database Performance Tuning Guide for the syntax and description of hints

DISTINCT | UNIQUE

Specify `DISTINCT` or `UNIQUE` if you want the database to return only one copy of each set of duplicate rows selected. These two keywords are synonymous. Duplicate rows are those with matching values for each expression in the select list.

Restrictions on DISTINCT and UNIQUE Queries These types of queries are subject to the following restrictions:

- When you specify `DISTINCT` or `UNIQUE`, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter `DB_BLOCK_SIZE`.

- You cannot specify `DISTINCT` if the `select_list` contains LOB columns.

ALL

Specify `ALL` if you want the database to return all rows selected, including all copies of duplicates. The default is `ALL`.

* (asterisk)

Specify the asterisk to select all columns from all tables, views, or materialized views listed in the `FROM` clause.

If you are selecting from a table rather than from a view or a materialized view, then columns that have been marked as `UNUSED` by the `ALTER` `TABLE` `SET` `UNUSED` statement are not selected.

See Also: ALTER TABLE, "Simple Query Examples", and "Selecting from the DUAL Table: Example"

select_list

The `select_list` lets you specify the columns you want to retrieve from the database.

query_name

For `query_name`, specify a name already specified in the `subquery_factoring_clause`. You must have specified the `subquery_factoring_clause` in order to specify `query_name` in the `select_list`. If you specify `query_name` in the `select_list`, then you also must specify `query_name` in the `query_table_expression` (`FROM` clause).

table.* | view.* | materialized view.*

Specify the object name followed by a period and the asterisk to select all columns from the specified table, view, or materialized view. Oracle Database returns a set of columns in the order in which the columns were specified when the object was created. A query that selects rows from two or more tables, views, or materialized views is a join.

You can use the schema qualifier to select from a table, view, or materialized view in a schema other than your own. If you omit `schema`, then the database assumes the table, view, or materialized view is in your own schema.

See Also: "Joins"

expr

Specify an expression representing the information you want to select. A column name in this list can be qualified with `schema` only if the table, view, or materialized view containing the column is qualified with `schema` in the `FROM` clause. If you specify a member method of an object type, then you must follow the method name with parentheses even if the method takes no arguments.

See Also: "Selecting Sequence Values: Examples"

c_alias Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The `AS` keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the `order_by_clause` but not other clauses in the query.

See Also: Oracle Data Warehousing Guide for information on using the `expr` `AS` `c_alias` syntax with the `UNION` `ALL` operator in queries of multiple materialized views "About SQL Expressions" for the syntax of `expr`

Restrictions on the Select List The select list is subject to the following restrictions:

- If you also specify a group_by_clause in this statement, then this select list can contain only the following types of expressions:

  - Constants

  - Aggregate functions and the functions `USER`, `UID`, and `SYSDATE`

  - Expressions identical to those in the `group_by_clause`. If the `group_by_clause` is in a subquery, then the `GROUP` `BY` columns of the subquery must match the select list of the outer query. Any columns in the select list of the subquery that are not needed by the `GROUP` `BY` operation are ignored without error.

  - Expressions involving the preceding expressions that evaluate to the same value for all rows in a group

- You can select a rowid from a join view only if the join has one and only one key-preserved table. The rowid of that table becomes the rowid of the view.

  See Also: Oracle Database Administrator's Guide for information on key-preserved tables

- If two or more tables have some column names in common, and if you are specifying a join in the `FROM` clause, then you must qualify column names with names of tables or table aliases.

FROM Clause

The `FROM` clause lets you specify the objects from which data is selected.

query_table_expression

Use the `query_table_expression` clause to identify a table, view, materialized view, partition, or subpartition, or to specify a subquery that identifies the objects.

See Also: "Using Subqueries: Examples"

ONLY The `ONLY` clause applies only to views. Specify `ONLY` if the view in the `FROM` clause is a view belonging to a hierarchy and you do not want to include rows from any of its subviews.

flashback_query_clause

Use the `flashback_query_clause` to retrieve past data from a table, view, or materialized view.

This clause implements SQL-driven Flashback, which lets you specify a different system change number or timestamp for each object in the select list. You can also implement session-level Flashback using the `DBMS_FLASHBACK` package.

A Flashback Query lets you retrieve a history of changes made to a row. You can retrieve the corresponding identifier of the transaction that made the change using the `VERSIONS_XID` pseudocolumn. You can also retrieve information about the transaction that resulted in a particular row version by issuing an Oracle Flashback Transaction Query. You do this by querying the `FLASHBACK_TRANSACTION_QUERY` data dictionary view for a particular transaction ID.

AS OF Specify `AS` `OF` to retrieve the single version of the rows returned by the query at a particular change number (SCN) or timestamp. If you specify `SCN`, then `expr` must evaluate to a number. If you specify `TIMESTAMP`, then `expr` must evaluate to a timestamp value. Oracle Database returns rows as they existed at the specified system change number or time.

VERSIONS Specify `VERSIONS` to retrieve multiple versions of the rows returned by the query. Oracle Database returns all committed versions of the rows that existed between two SCNs or between two timestamp values. The rows returned include deleted and subsequently reinserted versions of the rows.

- Specify `BETWEEN` `SCN` ... to retrieve the versions of the row that existed between two SCNs. Both expressions must evaluate to a number. `MINVALUE` and `MAXVALUE` resolve to the SCN of the oldest and most recent data available, respectively.

- Specify `BETWEEN` `TIMESTAMP` ... to retrieve the versions of the row that existed between two timestamps. Both expressions must evaluate to a timestamp value. `MINVALUE` and `MAXVALUE` resolve to the timestamp of the oldest and most recent data available, respectively.

Oracle Database provides a group of version query pseudocolumns that let you retrieve additional information about the various row versions. Please refer to "Version Query Pseudocolumns" for more information.

When both clauses are used together, the `AS` `OF` clause determines the SCN or moment in time from which the database issues the query. The `VERSIONS` clause determines the versions of the rows as seen from the `AS` `OF` point. The database returns null for a row version if the transaction started before the first `BETWEEN` value or ended after the `AS` `OF` point.

Restrictions on Flashback Queries These queries are subject to the following restrictions:

- You cannot specify a subquery in the expression of the `AS` `OF` clause.

- You cannot use the `VERSIONS` clause in flashback queries to temporary or external tables, or tables that are part of a cluster.

- You cannot use the `VERSIONS` clause in flashback queries to views. However, you can use the `VERSIONS` syntax in the defining query of a view.

- You cannot specify this clause if you have specified `query_name` in the `query_table_expression`.

See Also: Oracle Database Application Developer's Guide - Fundamentals for more information on Oracle Flashback Query "Using Flashback Queries: Example" Oracle Database Application Developer's Guide - Fundamentals and PL/SQL Packages and Types Reference for information about session-level Flashback using the `DBMS_FLASHBACK` package Oracle Database Administrator's Guide and to the description of `FLASHBACK_TRANSACTION_QUERY` in the Oracle Database Reference for more information about transaction history

PARTITION | SUBPARTITION For `PARTITION` or `SUBPARTITION`, specify the name of the partition or subpartition within `table` from which you want to retrieve data.

For range- and list-partitioned data, as an alternative to this clause, you can specify a condition in the `WHERE` clause that restricts the retrieval to one or more partitions of `table`. Oracle Database will interpret the condition and fetch data from only those partitions. It is not possible to formulate such a `WHERE` condition for hash-partitioned data.

See Also: "Referring to Partitioned Tables and Indexes" and "Selecting from a Partition: Example"

dblink For `dblink`, specify the complete or partial name for a database link to a remote database where the table, view, or materialized view is located. This database need not be an Oracle Database.

See Also: "Referring to Objects in Remote Databases" for more information on referring to database links "Distributed Queries" for more information about distributed queries and "Using Distributed Queries: Example"

If you omit `dblink`, then the database assumes that the table, view, or materialized view is on the local database.

Restrictions on Database Links Database links are subject to the following restrictions:

- You cannot query a user-defined type or an object `REF` on a remote table.

- You cannot query columns of type `ANYTYPE`, `ANYDATA`, or `ANYDATASET` from remote tables.

table | view | materialized view Specify the name of a table, view, or materialized view from which data is selected.

sample_clause

The `sample_clause` lets you instruct the database to select from a random sample of data from the table, rather than from the entire table.

See Also: "Selecting a Sample: Examples"

BLOCK `BLOCK` instructs the database to attempt to perform random block sampling instead of random row sampling.

Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then Oracle Database does not perform block sampling. If you want to guarantee block sampling for a particular table or index, then use the `FULL` or `INDEX_FFS` hint.

sample_percent For `sample_percent`, specify the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to, but not including, 100. This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly `sample_percent` of the rows of `table`.

Caution: The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.

SEED seed_value Specify this clause to instruct the database to attempt to return the same sample from one execution to the next. The `seed_value` must be an integer between 0 and 4294967295. If you omit this clause, then the resulting sample will change from one execution to the next.

Restriction on Sampling During Queries When sampling from a view, you must ensure that the view is key preserved. One way to do this is to use a `CREATE TABLE` ... `AS` `subquery` statement to materialize the result of an arbitrary query and then perform sampling on the resulting query.

Restrictions on sample_clause You cannot specify the `SAMPLE` clause in a subquery in a DML statement.

subquery_restriction_clause The `subquery_restriction_clause` lets you restrict the subquery in one of the following ways:

WITH READ ONLY Specify `WITH READ ONLY` to indicate that the table or view cannot be updated.

WITH CHECK OPTION Specify `WITH CHECK OPTION` to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the `FROM` clause but not in subquery in the `WHERE` clause.

CONSTRAINT constraint Specify the name of the `CHECK OPTION` constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form `SYS_C` `n`, where n is an integer that makes the constraint name unique within the database.

See Also: "Using the WITH CHECK OPTION Clause: Example"

table_collection_expression

The `table_collection_expression` lets you inform Oracle that the value of `collection_expression` should be treated as a table for purposes of query and DML operations. The `collection_expression` can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value—that is, a value whose type is nested table or varray. This process of extracting the elements of a collection is called collection unnesting.

The optional plus (+) is relevant if you are joining the `TABLE` expression with the parent table. The + creates an outer join of the two, so that the query returns rows from the outer table even if the collection expression is null.

Note: In earlier releases of Oracle, when `collection_expression` was a subquery, `table_collection_expression` was expressed as `THE` `subquery`. That usage is now deprecated.

The `collection_expression` can reference columns of tables defined to its left in the `FROM` clause. This is called left correlation. Left correlation can occur only in `table_collection_expression`. Other subqueries cannot contains references to columns defined outside the subquery.

The optional `(+)` lets you specify that `table_collection_expression` should return a row with all fields set to null if the collection is null or empty. The `(+)` is valid only if `collection_expression` uses left correlation. The result is similar to that of an outer join.

When you use the `(+)` syntax in the `WHERE` clause of a subquery in an `UPDATE` or `DELETE` operation, you must specify two tables in the `FROM` clause of the subquery. Oracle Database ignores the outer join syntax unless there is a join in the subquery itself.

See Also: "Outer Joins" "Table Collections: Examples" and "Collection Unnesting: Examples"

t_alias

Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.

See Also: "Using Correlated Subqueries: Examples"

join_clause

Use the appropriate `join_clause` syntax to identify tables that are part of a join from which to select data. The `inner_cross_join_clause` lets you specify an inner or cross join. The `outer_join_clause` lets you specify an outer join.

When you join more than two row sources, you can use parentheses to override default precedence. For example, the following syntax:

    SELECT ... FROM a JOIN (b JOIN c) ...

results in a join of `b` and `c`, and then a join of that result set with `a`.

See Also: "Joins" for more information on joins, "Using Join Queries: Examples", "Using Self Joins: Example", and "Using Outer Joins: Examples"

Inner Joins

Inner joins return only those rows that satisfy the join condition.

INNER Specify `INNER` to explicitly specify an inner join.

JOIN The `JOIN` keyword explicitly states that a join is being performed. You can use this syntax to replace the comma-delimited table expressions used in `WHERE` clause joins with `FROM` clause join syntax.

ON condition Use the `ON` clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the `WHERE` clause.

USING (column) When you are specifying an equijoin of columns that have the same name in both tables, the `USING` `column` clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Within this clause, do not qualify the column name with a table name or table alias.

Cross Joins

The `CROSS` keyword indicates that a cross join is being performed. A cross join produces the cross-product of two relations and is essentially the same as the comma-delimited Oracle Database notation.

Outer Joins

Outer joins return all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. You can specify two types of outer joins: a conventional outer join using the `table_reference` syntax on both sides of the join, or a partitioned outer join using the `query_partition_clause` on one side or the other. A partitioned outer join is similar to a conventional outer join except that the join takes place between the outer table and each partition of the inner table. This type of join lets you selectively make sparse data more dense along the dimensions of interest. This process is called data densification.

outer_join_type The `outer_join_type` indicates the kind of outer join being performed:

- Specify `RIGHT` to indicate a right outer join.

- Specify `LEFT` to indicate a left outer join.

- Specify `FULL` to indicate a full or two-sided outer join. In addition to the inner join, rows from both tables that have not been returned in the result of the inner join will be preserved and extended with nulls.

- You can specify the optional `OUTER` keyword following `RIGHT`, `LEFT`, or `FULL` to explicitly clarify that an outer join is being performed.

query_partition_clause The `query_partition_clause` lets you define a partitioned outer join. Such a join extends the conventional outer join syntax by applying the outer join to partitions returned by the query. Oracle Database creates a partition of rows for each expression you specify in the `PARTITION` `BY` clause. The rows in each query partition have same value for the `PARTITION` `BY` expression.

The `query_partition_clause` can be on either side of the outer join. The result of a partitioned outer join is a `UNION` of the outer joins of each of the partitions in the partitioned result set and the table on the other side of the join. This type of result is useful for filling gaps in sparse data, which simplifies analytic calculations.

If you omit this clause, then the database treats the entire table expression--that is, everything specified in `table_reference`--as a single partition, resulting in a conventional outer join.

To use the `query_partition_clause` in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the `model_column_clauses`) or a partitioned outer join (in the `outer_join_clause`), use the lower branch of the syntax (with parentheses).

Restrictions on Partitioned Outer Joins Partitioned outer joins are subject to the following restrictions:

- You can specify the `query_partition_clause` on either the right or left side of the join, but not both.

- You cannot specify a `FULL` partitioned outer join.

- If you specify the `query_partition_clause` in an outer join with an `ON` clause, then you cannot specify a subquery in the `ON` condition.

See Also: Oracle Data Warehousing Guide for a complete discussion of partitioned outer joins and data densification "Using Partitioned Outer Joins: Examples"

ON condition Use the `ON` clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the `WHERE` clause.

Restriction on the ON condition Clause You cannot specify this clause with a `NATURAL` outer join.

USING column In an outer join with the `USING` clause, the query returns a single column which is a coalesce of the two matching columns in the join. The coalesce functions as follows:

    COALESCE (a, b) = a if a NOT NULL, else b.

Therefore:

- A left outer join returns all the common column values from the left table in the `FROM` clause.

- A right outer join returns all the common column values from the right table in the `FROM` clause.

- A full outer join returns all the common column values from both joined tables.

Restriction on the USING column Clause

- Within this clause, do not qualify the column name with a table name or table alias.

- You cannot specify a LOB column or a collection column in the `USING` `column` clause.

- You cannot specify this clause with a `NATURAL` outer join.

See Also: "Using Outer Joins: Examples"

NATURAL JOIN The `NATURAL` keyword indicates that a natural join is being performed. A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

On occasion, the table pairings in natural or cross joins may be ambiguous. For example:

    a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1

can be interpreted in either of the following ways:

    a NATURAL LEFT JOIN (b LEFT JOIN c ON b.c1 = c.c1)
       (a NATURAL LEFT JOIN b) LEFT JOIN c ON b.c1 = c.c1

To avoid this ambiguity, you can use parentheses to specify the pairings of joined tables. In the absence of such parentheses, the database uses left associativity, pairing the tables from left to right.

Restriction on Natural Joins You cannot specify a LOB column, columns of `ANYTYPE`, `ANYDATA`, or `ANYDATASET`, or a collection column as part of a natural join.

where_clause

The `WHERE` condition lets you restrict the rows selected to those that satisfy one or more conditions. For `condition`, specify any valid SQL condition.

If you omit this clause, then the database returns all rows from the tables, views, or materialized views in the `FROM` clause.

Note: If this clause refers to a `DATE` column of a partitioned table or index, then the database performs partition pruning only if you created the table or index partitions by fully specifying the year using the `TO_DATE` function with a 4-digit format mask, and you specify the date in the `where_clause` of the query using the `TO_DATE` function and either a 2- or 4-digit format mask.

See Also: Chapter 7, "Conditions" for the syntax description of `condition` "Selecting from a Partition: Example"

hierarchical_query_clause

The `hierarchical_query_clause` lets you select rows in a hierarchical order.

`SELECT` statements that contain hierarchical queries can contain the `LEVEL` pseudocolumn in the select list. `LEVEL` returns the value 1 for a root node, 2 for a child node of a root node, 3 for a grandchild, and so on. The number of levels returned by a hierarchical query may be limited by available user memory.

Oracle processes hierarchical queries as follows:

- A join, if present, is evaluated first, whether the join is specified in the `FROM` clause or with `WHERE` clause predicates.

- The `CONNECT` `BY` condition is evaluated.

- Any remaining `WHERE` clause predicates are evaluated.

If you specify this clause, then do not specify either `ORDER` `BY` or `GROUP` `BY`, because they will destroy the hierarchical order of the `CONNECT` `BY` results. If you want to order rows of siblings of the same parent, then use the `ORDER` `SIBLINGS` `BY` clause.

See Also: "Hierarchical Queries" for a discussion of hierarchical queries and "Using the LEVEL Pseudocolumn: Examples"

START WITH Clause

Specify a condition that identifies the row(s) to be used as the root(s) of a hierarchical query. Oracle Database uses as root(s) all rows that satisfy this condition. If you omit this clause, then the database uses all rows in the table as root rows. The `START` `WITH` condition can contain a subquery, but it cannot contain a scalar subquery expression.

CONNECT BY Clause

Specify a condition that identifies the relationship between parent rows and child rows of the hierarchy. The `connect_by_condition` can be any condition as described in Chapter 7, "Conditions". However, it must use the `PRIOR` operator to refer to the parent row.

Restriction on the CONNECT BY Clause The `connect_by_condition` cannot contain a regular subquery or a scalar subquery expression.

See Also: Chapter 3, "Pseudocolumns" for more information on `LEVEL` "Hierarchical Queries" for general information on hierarchical queries "Hierarchical Query Examples"

group_by_clause

Specify the `GROUP` `BY` clause if you want the database to group the selected rows based on the value of `expr`(s) for each row and return a single row of summary information for each group. If this clause contains `CUBE` or `ROLLUP` extensions, then the database produces superaggregate groupings in addition to the regular groupings.

Expressions in the `GROUP` `BY` clause can contain any columns of the tables, views, or materialized views in the `FROM` clause, regardless of whether the columns appear in the select list.

The `GROUP` `BY` clause groups rows but does not guarantee the order of the result set. To order the groupings, use the `ORDER` `BY` clause.

See Also: Oracle Data Warehousing Guide for an expanded discussion and examples of using SQL grouping syntax for data aggregation the GROUP_ID, GROUPING, and GROUPING_ID functions for examples "Using the GROUP BY Clause: Examples"

ROLLUP The `ROLLUP` operation in the `simple_grouping_clause` groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the `GROUP` `BY` specification, and returns a single row of summary for each group. You can use the `ROLLUP` operation to produce subtotal values by using it with the `SUM` function. When used with `SUM`, `ROLLUP` generates subtotals from the most detailed level to the grand total. Aggregate functions such as `COUNT` can be used to produce other kinds of superaggregates.

For example, given three expressions (n=3) in the `ROLLUP` clause of the `simple_grouping_clause`, the operation results in n+1 = 3+1 = 4 groupings.

Rows grouped on the values of the first `n` expressions are called regular rows, and the others are called superaggregate rows.

See Also: Oracle Data Warehousing Guide for information on using `ROLLUP` with materialized views

CUBE The `CUBE` operation in the `simple_grouping_clause` groups the selected rows based on the values of all possible combinations of expressions in the specification. It returns a single row of summary information for each group. You can use the `CUBE` operation to produce cross-tabulation values.

For example, given three expressions (n=3) in the `CUBE` clause of the `simple_grouping_clause`, the operation results in 2n = 23 = 8 groupings. Rows grouped on the values of `n` expressions are called regular rows, and the rest are called superaggregate rows.

See Also: Oracle Data Warehousing Guide for information on using `CUBE` with materialized views "Using the GROUP BY CUBE Clause: Example"

GROUPING SETS `GROUPING` `SETS` are a further extension of the `GROUP` `BY` clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation by pruning the aggregates you do not need. You specify just the desired groups, and the database does not need to perform the full set of aggregations generated by `CUBE` or `ROLLUP`. Oracle Database computes all groupings specified in the `GROUPING` `SETS` clause and combines the results of individual groupings with a `UNION` `ALL` operation. The `UNION` `ALL` means that the result set can include duplicate rows.

Within the `GROUP` `BY` clause, you can combine expressions in various ways:

- To specify composite columns, group columns within parentheses so that the database treats them as a unit while computing `ROLLUP` or `CUBE` operations.

- To specify concatenated grouping sets, separate multiple grouping sets, `ROLLUP`, and `CUBE` operations with commas so that the database combines them into a single `GROUP` `BY` clause. The result is a cross-product of groupings from each grouping set.

See Also: "Using the GROUPING SETS Clause: Example"

HAVING Clause

Use the `HAVING` clause to restrict the groups of returned rows to those groups for which the specified `condition` is `TRUE`. If you omit this clause, then the database returns summary rows for all groups.

Specify `GROUP` `BY` and `HAVING` after the `where_clause` and `hierarchical_query_clause`. If you specify both `GROUP` `BY` and `HAVING`, then they can appear in either order.

See Also: "Using the HAVING Condition: Example"

Restrictions on the GROUP BY Clause: This clause is subject to the following restrictions:

- You cannot specify LOB columns, nested tables, or varrays as part of `expr`.

- The expressions can be of any form except scalar subquery expressions.

- If the `group_by_clause` references any object type columns, then the query will not be parallelized.

model_clause

The `model_clause` lets you view selected rows as a multidimensional array and randomly access cells within that array. Using the `model_clause`, you can specify a series of cell assignments, referred to as rules, that invoke calculations on individual cells and ranges of cells. These rules operate on the results of a query and do not update any database tables.

When using the `model_clause` in a query, the `SELECT` and `ORDER` `BY` clauses must refer only to those columns defined in the `model_column_clauses`.

See Also: the syntax description of `expr` in "About SQL Expressions" and the syntax description of `condition` in Chapter 7, "Conditions" Oracle Data Warehousing Guide for an expanded discussion and examples "The MODEL clause: Examples"

main_model

The `main_model` clause defines how the selected rows will be viewed in a multidimensional array and what rules will operate on which cells in that array.

model_column_clauses

The `model_column_clauses` define and classify the columns of a query into three groups: partition columns, dimension columns, and measure columns.

PARTITION BY The `PARTITION` `BY` clause specifies the columns that will be used to divide the selected rows into partitions based on the values of the specified columns.

DIMENSION BY The `DIMENSION` `BY` clause specifies the columns that will identify a row within a partition. The values of the dimension columns, along with those of the partition columns, serve as array indexes to the measure columns within a row.

MEASURES The `MEASURES` clause identifies the columns on which the calculations can be performed. Measure columns in individual rows are treated like cells that you can reference, by specifying the values for the partition and dimension columns, and update.

model_column `model_column` identifies a column to be used in defining the model. A column alias is required if `expr` is not a column name. Please refer to "Model Expressions" for information on model expressions.

cell_reference_options

Use the `cell_reference_options` clause to specify how null and absent values are treated in rules and how column uniqueness is constrained.

IGNORE NAV When you specify `IGNORE` `NAV`, the database returns the following values for the null and absent values of the datatype specified:

- Zero for numeric datatypes

- 01-JAN-2000 for datetime datatypes

- An empty string for character datatypes

- Null for all other datatypes

KEEP NAV When you specify `KEEP` `NAV`, the database returns null for both null and absent cell values. `KEEP` `NAV` is the default.

UNIQUE SINGLE REFERENCE When you specify `UNIQUE` `SINGLE` `REFERENCE`, the database checks only single-cell references on the right-hand side of the rule for uniqueness, not the entire query result set.

UNIQUE DIMENSION When you specify `UNIQUE` `DIMENSION`, the database checks that the `PARTITION` `BY` and `DIMENSION` `BY` columns form a unique key to the query. `UNIQUE` `DIMENSION` is the default.

model_rules_clause

Use the `model_rules_clause` to specify the cells to be updated, the rules for updating those cells, and optionally, how the rules are to be applied and processed.

Each rule represents an assignment and consists of a left-hand side and right-hand side. The left-hand side of the rule identifies the cells to be updated by the right-hand side of the rule. The right-hand side of the rule evaluates to the values to be assigned to the cells specified on the left-hand side of the rule.

UPSERT ALL `UPSERT` `ALL` allows `UPSERT` behavior for a rule with both positional and symbolic references on the left-hand side of the rule. When evaluating an `UPSERT` `ALL` rule, Oracle performs the following steps to create a list of cell references to be upserted:

1. Find the existing cells that satisfy all the symbolic predicates of the cell reference.

2. Using just the dimensions that have symbolic references, find the distinct dimension value combinations of these cells.

3. Perform a cross product of these value combinations with the dimension values specified by way of positional references.

Please refer to Oracle Data Warehousing Guide for more information on the semantics of `UPSERT` `ALL`.

UPSERT When you specify `UPSERT`, the database applies the rules to those cells referenced on the left-hand side of the rule that exist in the multidimensional array, and inserts new rows for those that do not exist. `UPSERT` behavior applies only when positional referencing is used on the left-hand side and a single cell is referenced. `UPSERT` is the default. Please refer to cell_assignment for more information on positional referencing and single-cell references.

`UPDATE` and `UPSERT` can be specified for individual rules as well. When either `UPDATE` or `UPSERT` is specified for a specific rule, it takes precedence over the option specified in the `RULES` clause.

Notes on UPSERT [ALL] and UPDATE: If an `UPSERT` `ALL`, `UPSERT`, or `UPDATE` rule does not contain the appropriate predicates, the database may implicitly convert it to a different type of rule: If an `UPSERT` rule contains an existential predicate, then the rule is treated as an `UPDATE` rule. An `UPSERT` `ALL` rule must have at least one existential predicate and one qualified predicate on its left side. If it has no existential predicate, then it is treated as an `UPSERT` rule. If it has no qualified predicate, then it is treated as an `UPDATE` rule.

UPDATE When you specify `UPDATE`, the database applies the rules to those cells referenced on the left-hand side of the rule that exist in the multidimensional array. If the cells do not exist, the assignment is ignored.

AUTOMATIC ORDER When you specify `AUTOMATIC` `ORDER`, the database evaluates the rules based on their dependency order. In this case, a cell can be assigned a value once only.

SEQUENTIAL ORDER When you specify `SEQUENTIAL` `ORDER`, the database evaluates the rules in the order they appear. In this case, a cell can be assigned a value more than once. `SEQUENTIAL` `ORDER` is the default.

ITERATE...[UNTIL] Use `ITERATE`...[`UNTIL`] to specify the number of times to cycle through the rules and, optionally, an early termination condition.

When you specify `ITERATE`...[`UNTIL`], rules are evaluated in the order in which they appear. Oracle Database returns an error if both `AUTOMATIC` `ORDER` and `ITERATE`...`[UNTIL]` are specified in the `model_rules_clause`.

cell_assignment

The `cell_assignment` clause, which is the left-hand side of the rule, specifies one or more cells to be updated. When a `cell_assignment` references a single cell, it is called a single-cell reference. When more than one cell is referenced, it is called a multiple-cell reference.

All dimension columns defined in the `model_clause` must be qualified in the `cell_assignment` clause. A dimension can be qualified using either symbolic or positional referencing.

A symbolic reference qualifies a single dimension column using a Boolean condition like `dimension_column``=``constant`. A positional reference is one where the dimension column is implied by its position in the `DIMENSION` `BY` clause. The only difference between symbolic references and positional references is in the treatment of nulls.

Using a single-cell symbolic reference such as `a[x=null,y=2000]`, no cells qualify because `x=null` evaluates to `FALSE`. However, using a single-cell positional reference such as `a[null,2000]`, a cell where `x` is null and `y` is 2000 qualifies because null = null evaluates to `TRUE`. With single-cell positional referencing, you can reference, update, and insert cells where dimension columns are null.

You can specify a condition or an expression representing a dimension column value using either symbolic or positional referencing. `condition` cannot contain aggregate functions or the `CV` function, and `condition` must reference a single dimension column. `expr` cannot contain a subquery. Please refer to "Model Expressions" for information on model expressions.

single_column_for_loop

The `single_column_for_loop` clause lets you specify a range of cells to be updated within a single dimension column.

The `IN` clause lets you specify the values of the dimension column as either a list of values or as a subquery. When using `subquery`, it cannot:

- Be a correlated query

- Return more than 10,000 rows

- Be a query defined in the `WITH` clause

The `FROM` clause lets you specify a range of values for a dimension column with discrete increments within the range. The `FROM` clause can only be used for those columns with a datatype for which addition and subtraction is supported. The `INCREMENT` and `DECREMENT` values must be positive.

Optionally, you can specify the `LIKE` clause within the `FROM` clause. In the `LIKE` clause, `pattern` is a character string containing a single pattern-matching character `%`. This character is replaced during execution with the current incremented or decremented value in the `FROM` clause.

If all dimensions other than those used by a `FOR` loop involve a single-cell reference, then the expressions can insert new rows. The number of dimension value combinations generated by `FOR` loops is counted as part of the 10,000 row limit of the `MODEL` clause.

multi_column_for_loop

The `multi_column_for_loop` clause lets you specify a range of cells to be updated across multiple dimension columns. The `IN` clause lets you specify the values of the dimension columns as either multiple lists of values or as a subquery. When using `subquery`, it cannot:

- Be a correlated query

- Return more than 10,000 rows

- Be a query defined in the `WITH` clause

If all dimensions other than those used by a `FOR` loop involve a single-cell reference, then the expressions can insert new rows. The number of dimension value combinations generated by `FOR` loops is counted as part of the 10,000 row limit of the `MODEL` clause.

See Also: Oracle Data Warehousing Guide for more information about using `FOR` loops in the `MODEL` clause

order_by_clause

Use the `ORDER` `BY` clause to specify the order in which cells on the left-hand side of the rule are to be evaluated. The `expr` must resolve to a dimension or measure column. If the `ORDER` `BY` clause is not specified, the order defaults to the order of the columns as specified in the `DIMENSION` `BY` clause. See order_by_clause for more information.

Restrictions on the order_by_clause Use of the `ORDER` `BY` clause in the model rule is subject to the following restrictions:

- You cannot specify `SIBLINGS`, `position`, or `c_alias` in the `order_by_clause` of the `model_clause`.

- You cannot specify this clause on the left-hand side of the model rule and also specify a `FOR` loop on the right-hand side of the rule.

expr

Specify an expression representing the value or values of the cell or cells specified on the right-hand side of the rule. `expr` cannot contain a subquery. Please refer to "Model Expressions" for information on model expressions.

return_rows_clause

The `return_rows_clause` lets you specify whether to return all rows selected or only those rows updated by the model rules. `ALL` is the default.

reference_model

Use the `reference_model` clause when you need to access multiple arrays from inside the `model_clause`. This clause defines a read-only multidimensional array based on the results of a query.

The subclauses of the `reference_model` clause have the same semantics as for the `main_model` clause. Please refer to cell_reference_options, model_column_clauses, and cell_reference_options.

Restrictions on the reference_model clause This clause is subject to the following restrictions:

- `PARTITION` `BY` columns cannot be specified for reference models.

- The subquery of the reference model cannot refer to columns in an outer subquery.

Set Operators: UNION, UNION ALL, INTERSECT, MINUS ``

The set operators combine the rows returned by two `SELECT` statements into a single result. The number and datatypes of the columns selected by each component query must be the same, but the column lengths can be different. The names of the columns in the result set are the names of the expressions in the select list preceding the set operator.

If you combine more than two queries with set operators, then the database evaluates adjacent queries from left to right. You can use parentheses to specify a different order of evaluation.

Please refer to "The UNION [ALL], INTERSECT, MINUS Operators" for information on these operators, including restrictions on their use.

order_by_clause

Use the `ORDER` `BY` clause to order rows returned by the statement. Without an `order_by_clause`, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

SIBLINGS The `SIBLINGS` keyword is valid only if you also specify the `hierarchical_query_clause` (`CONNECT` `BY`). `ORDER` `SIBLINGS` `BY` preserves any ordering specified in the hierarchical query clause and then applies the `order_by_clause` to the siblings of the hierarchy.

expr `expr` orders rows based on their value for `expr`. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the `FROM` clause.

position Specify `position` to order rows based on their value for the expression in this position of the select list. The `position` value must be an integer.

You can specify multiple expressions in the `order_by_clause`. Oracle Database first sorts rows based on their values for the first expression. Rows with the same value for the first expression are then sorted based on their values for the second expression, and so on. The database sorts nulls following all others in ascending order and preceding all others in descending order. Please refer to "Sorting Query Results" for a discussion of ordering query results.

ASC | DESC Specify whether the ordering sequence is ascending or descending. `ASC` is the default.

NULLS FIRST | NULLS LAST Specify whether returned rows containing null values should appear first or last in the ordering sequence.

`NULLS` `LAST` is the default for ascending order, and `NULLS` `FIRST` is the default for descending order.

Restrictions on the ORDER BY Clause The following restrictions apply to the `ORDER` `BY` clause:

- If you have specified the `DISTINCT` operator in this statement, then this clause cannot refer to columns unless they appear in the select list.

- An `order_by_clause` can contain no more than 255 expressions.

- You cannot order by a LOB column, nested table, or varray.

- If you specify a group_by_clause in the same statement, then this `order_by_clause` is restricted to the following expressions:

  - Constants

  - Aggregate functions

  - Analytic functions

  - The functions `USER`, `UID`, and `SYSDATE`

  - Expressions identical to those in the `group_by_clause`

  - Expressions comprising the preceding expressions that evaluate to the same value for all rows in a group

See Also: "Using the ORDER BY Clause: Examples"

for_update_clause

The `FOR` `UPDATE` clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level `SELECT` statement, not in subqueries.

Note: Prior to updating a LOB value, you must lock the row containing the LOB. One way to lock the row is with an embedded `SELECT` ... `FOR` `UPDATE` statement. You can do this using one of the programmatic languages or `DBMS_LOB` package. For more information on lock rows before writing to a LOB, see Oracle Database Application Developer's Guide - Large Objects.

Nested table rows are not locked as a result of locking the parent table rows. If you want the nested table rows to be locked, then you must lock them explicitly.

Restrictions on the FOR UPDATE Clause This clause is subject to the following restrictions:

- You cannot specify this clause with the following other constructs: the `DISTINCT` operator, `CURSOR` expression, set operators, `group_by_clause`, or aggregate functions.

- The tables locked by this clause must all be located on the same database and on the same database as any `LONG` columns and sequences referenced in the same statement.

See Also: "Using the FOR UPDATE Clause: Examples"

OF ... column

Use the `OF` ... `column` clause to lock the select rows only for a particular table or view in a join. The columns in the `OF` clause only indicate which table or view rows are locked. The specific columns that you specify are not significant. However, you must specify an actual column name, not a column alias. If you omit this clause, then the database locks the selected rows from all the tables in the query.

NOWAIT | WAIT

The `NOWAIT` and `WAIT` clauses let you tell the database how to proceed if the `SELECT` statement attempts to lock a row that is locked by another user.

NOWAIT Specify `NOWAIT` to return control to you immediately if a lock exists.

WAIT Specify `WAIT` to instruct the database to wait `integer` seconds for the row to become available and then return control to you.

If you specify neither `WAIT` nor `NOWAIT`, then the database waits until the row is available and then returns the results of the `SELECT` statement.

Restriction on the WAIT Clause

  If you specify `WAIT` and the table is locked in exclusive mode, then the database will not return the results of the `SELECT` statement until the lock on the table is released regardless of the wait time specified.

Examples

Subquery Factoring: Example The following statement creates the query names `dept_costs` and `avg_cost` for the initial query block containing a join, and then uses the query names in the body of the main query.

    WITH
       dept_costs AS (
          SELECT department_name, SUM(salary) dept_total
             FROM employees e, departments d
             WHERE e.department_id = d.department_id
          GROUP BY department_name),
       avg_cost AS (
          SELECT SUM(dept_total)/COUNT(*) avg
          FROM dept_costs)
    SELECT * FROM dept_costs
       WHERE dept_total >
          (SELECT avg FROM avg_cost)
          ORDER BY department_name;

    DEPARTMENT_NAME                DEPT_TOTAL
    ------------------------------ ----------
    Sales                              313800
    Shipping                           156400

Simple Query Examples The following statement selects rows from the `employees` table with the department number of 30:

    SELECT *
       FROM employees
       WHERE department_id = 30
       ORDER BY last_name;

The following statement selects the name, job, salary and department number of all employees except purchasing clerks from department number 30:

    SELECT last_name, job_id, salary, department_id
       FROM employees
       WHERE NOT (job_id = 'PU_CLERK' AND department_id = 30)
       ORDER BY last_name;

The following statement selects from subqueries in the `FROM` clause and for each department returns the total employees and salaries as a decimal value of all the departments:

    SELECT a.department_id "Department",
       a.num_emp/b.total_count "%_Employees",
       a.sal_sum/b.total_sal "%_Salary"
    FROM
    (SELECT department_id, COUNT(*) num_emp, SUM(salary) sal_sum
       FROM employees
       GROUP BY department_id) a,
    (SELECT COUNT(*) total_count, SUM(salary) total_sal
       FROM employees) b
    ORDER BY a.department_id;

Selecting from a Partition: Example You can select rows from a single partition of a partitioned table by specifying the keyword `PARTITION` in the `FROM` clause. This SQL statement assigns an alias for and retrieves rows from the `sales_q2_2000` partition of the sample table `sh.sales`:

    SELECT * FROM sales PARTITION (sales_q2_2000) s
       WHERE s.amount_sold > 1500
       ORDER BY cust_id, time_id, channel_id;

The following example selects rows from the `oe.orders` table for orders earlier than a specified date:

    SELECT * FROM orders
       WHERE order_date < TO_DATE('2000-06-15', 'YYYY-MM-DD');

Selecting a Sample: Examples The following query estimates the number of orders in the `oe.orders` table:

    SELECT COUNT(*) * 10 FROM orders SAMPLE (10);

    COUNT(*)*10
    -----------
             70

Because the query returns an estimate, the actual return value may differ from one query to the next.

    SELECT COUNT(*) * 10 FROM orders SAMPLE (10);

    COUNT(*)*10
    -----------
             80

The following query adds a seed value to the preceding query. Oracle Database always returns the same estimate given the same seed value:

    SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED (1);

    COUNT(*)*10
    -----------
            110

    SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED(4);

    COUNT(*)*10
    -----------
            120

    SELECT COUNT(*) * 10 FROM orders SAMPLE(10) SEED (1);

    COUNT(*)*10
    -----------
            110

Using Flashback Queries: Example The following statements show a current value from the sample table `hr.employees` and then change the value. The intervals used in these examples are very short for demonstration purposes. Time intervals in your own environment are likely to be larger.

    SELECT salary FROM employees
       WHERE last_name = 'Chung';

        SALARY
    ----------
          3800

    UPDATE employees SET salary = 4000
       WHERE last_name = 'Chung';
    1 row updated.

    SELECT salary FROM employees
       WHERE last_name = 'Chung';

        SALARY
    ----------
          4000

To learn what the value was before the update, you can use the following Flashback Query:

    SELECT salary FROM employees
       AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE)
       WHERE last_name = 'Chung';

        SALARY
    ----------
          3800

To learn what the values were during a particular time period, you can use a version Flashback Query:

    SELECT salary FROM employees
      VERSIONS BETWEEN TIMESTAMP
        SYSTIMESTAMP - INTERVAL '10' MINUTE AND
        SYSTIMESTAMP - INTERVAL '1' MINUTE
      WHERE last_name = 'Chung';

To revert to the earlier value, use the Flashback Query as the subquery of another `UPDATE` statement:

    UPDATE employees SET salary =
       (SELECT salary FROM employees
       AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' MINUTE)
       WHERE last_name = 'Chung')
       WHERE last_name = 'Chung';
    1 row updated.

    SELECT salary FROM employees
       WHERE last_name = 'Chung';

        SALARY
    ----------
          3800

Using the GROUP BY Clause: Examples To return the minimum and maximum salaries for each department in the `employees` table, issue the following statement:

    SELECT department_id, MIN(salary), MAX (salary)
         FROM employees
         GROUP BY department_id
       ORDER BY department_id;

To return the minimum and maximum salaries for the clerks in each department, issue the following statement:

    SELECT department_id, MIN(salary), MAX (salary)
         FROM employees
         WHERE job_id = 'PU_CLERK'
         GROUP BY department_id
       ORDER BY department_id;

Using the GROUP BY CUBE Clause: Example To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query on the sample tables `hr.employees` and `hr.departments`:

    SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
          department_name) AS department_name,
       DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job_id,
       COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
       FROM employees e, departments d
       WHERE d.department_id = e.department_id
       GROUP BY CUBE (department_name, job_id)
       ORDER BY department_name, job_id;

    DEPARTMENT_NAME                JOB_ID     Total Empl Average Sal
    ------------------------------ ---------- ---------- -----------
    Accounting                     AC_ACCOUNT          1       99600
    Accounting                     AC_MGR              1      144000
    Accounting                     All Jobs            2      121800
    Administration                 AD_ASST             1       52800
    . . .
    All Departments                ST_MAN              5       87360
    All Departments                All Jobs          107  77798.1308

Using the GROUPING SETS Clause: Example The following example finds the sum of sales aggregated for three precisely specified groups:

- `(channel_desc, calendar_month_desc, country_id)`

- `(channel_desc, country_id)`

- `(calendar_month_desc, country_id)`

Without the `GROUPING` `SETS` syntax, you would have to write less efficient queries with more complicated SQL. For example, you could run three separate queries and `UNION` them, or run a query with a `CUBE(channel_desc, calendar_month_desc, country_id)` operation and filter out five of the eight groups it would generate.

    SELECT channel_desc, calendar_month_desc, co.country_id,
          TO_CHAR(sum(amount_sold) , '9,999,999,999') SALES$
       FROM sales, customers, times, channels, countries co
       WHERE sales.time_id=times.time_id
          AND sales.cust_id=customers.cust_id
          AND sales.channel_id= channels.channel_id
          AND customers.country_id = co.country_id
          AND channels.channel_desc IN ('Direct Sales', 'Internet')
          AND times.calendar_month_desc IN ('2000-09', '2000-10')
          AND co.country_iso_code IN ('UK', 'US')
      GROUP BY GROUPING SETS(
          (channel_desc, calendar_month_desc, co.country_id),
          (channel_desc, co.country_id),
          (calendar_month_desc, co.country_id) );

    CHANNEL_DESC         CALENDAR CO SALES$
    -------------------- -------- -- --------------
    Direct Sales         2000-09  UK      1,378,126
    Direct Sales         2000-10  UK      1,388,051
    Direct Sales         2000-09  US      2,835,557
    Direct Sales         2000-10  US      2,908,706
    Internet             2000-09  UK        911,739
    Internet             2000-10  UK        876,571
    Internet             2000-09  US      1,732,240
    Internet             2000-10  US      1,893,753
    Direct Sales                  UK      2,766,177
    Direct Sales                  US      5,744,263
    Internet                      UK      1,788,310
    Internet                      US      3,625,993
                         2000-09  UK      2,289,865
                         2000-09  US      4,567,797
                         2000-10  UK      2,264,622
                         2000-10  US      4,802,459

See Also: the functions GROUP_ID, GROUPING, and GROUPING_ID for more information on those functions

Hierarchical Query Examples The following query with a `CONNECT` `BY` clause defines a hierarchical relationship in which the `employee_id` value of the parent row is equal to the `manager_id` value of the child row:

    SELECT last_name, employee_id, manager_id FROM employees
       CONNECT BY employee_id = manager_id
       ORDER BY last_name;

In the following `CONNECT` `BY` clause, the `PRIOR` operator applies only to the `employee_id` value. To evaluate this condition, the database evaluates `employee_id` values for the parent row and `manager_id`, `salary`, and `commission_pct` values for the child row:

    SELECT last_name, employee_id, manager_id FROM employees
       CONNECT BY PRIOR employee_id = manager_id
       AND salary > commission_pct
       ORDER BY last_name;

To qualify as a child row, a row must have a `manager_id` value equal to the `employee_id` value of the parent row and it must have a `salary` value greater than its `commission_pct` value.

Using the HAVING Condition: Example To return the minimum and maximum salaries for the employees in each department whose lowest salary is less than $5,000, issue the next statement:

    SELECT department_id, MIN(salary), MAX (salary)
       FROM employees
       GROUP BY department_id
       HAVING MIN(salary) < 5000
       ORDER BY department_id;

    DEPARTMENT_ID MIN(SALARY) MAX(SALARY)
    ------------- ----------- -----------
               10        4400        4400
               30        2500       11000
               50        2100        8200
               60        4200        9000

The following example uses a correlated subquery in a `HAVING` clause that eliminates from the result set any departments without managers and managers without departments:

    SELECT department_id, manager_id
       FROM employees
       GROUP BY department_id, manager_id HAVING (department_id, manager_id) IN   (SELECT department_id, manager_id FROM employees x
          WHERE x.department_id = employees.department_id)
       ORDER BY department_id;

Using the ORDER BY Clause: Examples To select all purchasing clerk records from `employees` and order the results by commission in descending order, issue the following statement:

    SELECT *
       FROM employees
       WHERE job_id = 'PU_CLERK'
       ORDER BY commission_pct DESC;

To select information from `employees` ordered first by ascending department number and then by descending salary, issue the following statement:

    SELECT last_name, department_id, salary
       FROM employees
       ORDER BY department_id ASC, salary DESC;

To select the same information as the previous `SELECT` and use the positional `ORDER` `BY` notation, issue the following statement, which orders by ascending `department_id`, then descending `salary`, and finally alphabetically by `last_name`:

    SELECT last_name, department_id, salary
       FROM employees
       ORDER BY 2 ASC, 3 DESC, 1;

The MODEL clause: Examples The view created below is based on the sample `sh` schema and is used by the example that follows.

    CREATE OR REPLACE VIEW sales_view_ref AS
      SELECT country_name country,
             prod_name prod,
             calendar_year year,
             SUM(amount_sold) sale,
             COUNT(amount_sold) cnt
        FROM sales,times,customers,countries,products
        WHERE sales.time_id = times.time_id AND
              sales.prod_id = products.prod_id AND
              sales.cust_id = customers.cust_id AND
              customers.country_id = countries.country_id AND
              ( customers.country_id = 52779 OR
                customers.country_id = 52776 ) AND
              ( prod_name = 'Standard Mouse' OR
                prod_name = 'Mouse Pad' )
        GROUP BY country_name,prod_name,calendar_year;

    SELECT country, prod, year, sale
      FROM sales_view_ref
      ORDER BY country, prod, year;

    COUNTRY       PROD                                         YEAR        SALE
    ----------    -----------------------------------      --------   ---------
    France        Mouse Pad                                    1998     2509.42
    France        Mouse Pad                                    1999     3678.69
    France        Mouse Pad                                    2000     3000.72
    France        Mouse Pad                                    2001     3269.09
    France        Standard Mouse                               1998     2390.83
    France        Standard Mouse                               1999     2280.45
    France        Standard Mouse                               2000     1274.31
    France        Standard Mouse                               2001     2164.54
    Germany       Mouse Pad                                    1998     5827.87
    Germany       Mouse Pad                                    1999     8346.44
    Germany       Mouse Pad                                    2000     7375.46
    Germany       Mouse Pad                                    2001     9535.08
    Germany       Standard Mouse                               1998     7116.11
    Germany       Standard Mouse                               1999     6263.14
    Germany       Standard Mouse                               2000     2637.31
    Germany       Standard Mouse                               2001     6456.13

    16 rows selected.

The next example creates a multidimensional array from `sales_view_ref` with columns containing country, product, year, and sales. It also:

- Assigns the sum of the sales of the Mouse Pad for years 1999 and 2000 to the sales of the Mouse Pad for year 2001, if a row containing sales of the Mouse Pad for year 2001 exists.

- Assigns the value of sales of the Standard Mouse for year 2001 to sales of the Standard Mouse for year 2002, creating a new row if a row containing sales of the Standard Mouse for year 2002 does not exist.

    SELECT country,prod,year,s
      FROM sales_view_ref
      MODEL
        PARTITION BY (country)
        DIMENSION BY (prod, year)
        MEASURES (sale s)
        IGNORE NAV
        UNIQUE DIMENSION
        RULES UPSERT SEQUENTIAL ORDER
        (
          s[prod='Mouse Pad', year=2001] =
            s['Mouse Pad', 1999] + s['Mouse Pad', 2000],
          s['Standard Mouse', 2002] = s['Standard Mouse', 2001]
        )
      ORDER BY country, prod, year;

    COUNTRY       PROD                                         YEAR        SALE
    ----------    -----------------------------------      --------   ---------
    France        Mouse Pad                                    1998     2509.42
    France        Mouse Pad                                    1999     3678.69
    France        Mouse Pad                                    2000     3000.72
    France        Mouse Pad                                    2001     6679.41
    France        Standard Mouse                               1998     2390.83
    France        Standard Mouse                               1999     2280.45
    France        Standard Mouse                               2000     1274.31
    France        Standard Mouse                               2001     2164.54
    France        Standard Mouse                               2002     2164.54
    Germany       Mouse Pad                                    1998     5827.87
    Germany       Mouse Pad                                    1999     8346.44
    Germany       Mouse Pad                                    2000     7375.46
    Germany       Mouse Pad                                    2001     15721.9
    Germany       Standard Mouse                               1998     7116.11
    Germany       Standard Mouse                               1999     6263.14
    Germany       Standard Mouse                               2000     2637.31
    Germany       Standard Mouse                               2001     6456.13
    Germany       Standard Mouse                               2002     6456.13

    18 rows selected.

The first rule uses `UPDATE` behavior because symbolic referencing is used on the left-hand side of the rule. The rows represented by the left-hand side of the rule exist, so the measure columns are updated. If the rows did not exist, then no action would have been taken.

The second rule uses `UPSERT` behavior because positional referencing is used on the left-hand side and a single cell is referenced. The rows do not exist, so new rows are inserted and the related measure columns are updated. If the rows did exist, then the measure columns would have been updated.

See Also: Oracle Data Warehousing Guide for an expanded discussion and examples

The next example uses the same `sales_view_ref` view and the analytic function `SUM` to calculate a cumulative sum (`csum`) of sales per country and per year.

    SELECT country, year, sale, csum
       FROM
       (SELECT country, year, SUM(sale) sale
        FROM sales_view_ref
        GROUP BY country, year
       )
       MODEL DIMENSION BY (country, year)
             MEASURES (sale, 0 csum)
             RULES (csum[any, any]=
                      SUM(sale) OVER (PARTITION BY country
                                      ORDER BY year
                                      ROWS UNBOUNDED PRECEDING)
                    )
       ORDER BY country, year;

    COUNTRY               YEAR       SALE       CSUM
    --------------- ---------- ---------- ----------
    France                1998    4900.25    4900.25
    France                1999    5959.14   10859.39
    France                2000    4275.03   15134.42
    France                2001    5433.63   20568.05
    Germany               1998   12943.98   12943.98
    Germany               1999   14609.58   27553.56
    Germany               2000   10012.77   37566.33
    Germany               2001   15991.21   53557.54

    8 rows selected.

Using the FOR UPDATE Clause: Examples The following statement locks rows in the `employees` table with purchasing clerks located in Oxford, which has `location_id` 2500, and locks rows in the `departments` table with departments in Oxford that have purchasing clerks:

    SELECT e.employee_id, e.salary, e.commission_pct
       FROM employees e, departments d
       WHERE job_id = 'SA_REP'
       AND e.department_id = d.department_id
       AND location_id = 2500
       FOR UPDATE
       ORDER BY e.employee_id;

The following statement locks only those rows in the `employees` table with purchasing clerks located in Oxford. No rows are locked in the `departments` table:

    SELECT e.employee_id, e.salary, e.commission_pct
       FROM employees e JOIN departments d
       USING (department_id)
       WHERE job_id = 'SA_REP'
       AND location_id = 2500
       FOR UPDATE OF e.salary
       ORDER BY e.employee_id;

Using the WITH CHECK OPTION Clause: Example The following statement is legal even though the third value inserted violates the condition of the subquery `where_clause`:

    INSERT INTO (SELECT department_id, department_name, location_id
       FROM departments WHERE location_id < 2000)
       VALUES (9999, 'Entertainment', 2500);

However, the following statement is illegal because it contains the `WITH` `CHECK` `OPTION` clause:

    INSERT INTO (SELECT department_id, department_name, location_id
       FROM departments WHERE location_id < 2000 WITH CHECK OPTION)
       VALUES (9999, 'Entertainment', 2500);
         *
    ERROR at line 2:
    ORA-01402: view WITH CHECK OPTION where-clause violation

Using Join Queries: Examples The following examples show various ways of joining tables in a query. In the first example, an equijoin returns the name and job of each employee and the number and name of the department in which the employee works:

    SELECT last_name, job_id, departments.department_id, department_name
       FROM employees, departments
       WHERE employees.department_id = departments.department_id
       ORDER BY last_name;

    LAST_NAME           JOB_ID     DEPARTMENT_ID DEPARTMENT_NAME
    ------------------- ---------- ------------- ----------------------
    . . .
    Sciarra             FI_ACCOUNT           100 Finance
    Urman               FI_ACCOUNT           100 Finance
    Popp                FI_ACCOUNT           100 Finance
    . . .

You must use a join to return this data because employee names and jobs are stored in a different table than department names. Oracle Database combines rows of the two tables according to this join condition:

    employees.department_id = departments.department_id

The following equijoin returns the name, job, department number, and department name of all sales managers:

    SELECT last_name, job_id, departments.department_id, department_name
       FROM employees, departments
       WHERE employees.department_id = departments.department_id
       AND job_id = 'SA_MAN'
       ORDER BY last_name;

    LAST_NAME           JOB_ID     DEPARTMENT_ID DEPARTMENT_NAME
    ------------------- ---------- ------------- -----------------------
    Russell             SA_MAN                80 Sales
    Partners            SA_MAN                80 Sales
    Errazuriz           SA_MAN                80 Sales
    Cambrault           SA_MAN                80 Sales
    Zlotkey             SA_MAN                80 Sales

This query is identical to the preceding example, except that it uses an additional `where_clause` condition to return only rows with a `job` value of '`SA_MAN`'.

Using Subqueries: Examples To determine who works in the same department as employee '`Lorentz`', issue the following statement:

    SELECT last_name, department_id FROM employees
       WHERE department_id =
         (SELECT department_id FROM employees
          WHERE last_name = 'Lorentz')
       ORDER BY last_name;

To give all employees in the `employees` table a 10% raise if they have changed jobs--that is, if they appear in the `job_history` table--issue the following statement:

    UPDATE employees
        SET salary = salary * 1.1
        WHERE employee_id IN (SELECT employee_id FROM job_history);

To create a second version of the `departments` table `new_departments`, with only three of the columns of the original table, issue the following statement:

    CREATE TABLE new_departments
       (department_id, department_name, location_id)
       AS SELECT department_id, department_name, location_id
       FROM departments;

Using Self Joins: Example The following query uses a self join to return the name of each employee along with the name of the employee's manager. A `WHERE` clause is added to shorten the output.

    SELECT e1.last_name||' works for '||e2.last_name
       "Employees and Their Managers"
       FROM employees e1, employees e2
       WHERE e1.manager_id = e2.employee_id
          AND e1.last_name LIKE 'R%';

    Employees and Their Managers
    -------------------------------
    Rajs works for Mourgos
    Raphaely works for King
    Rogers works for Kaufling
    Russell works for King

The join condition for this query uses the aliases `e1` and `e2` for the sample table `employees`:

    e1.manager_id = e2.employee_id

Using Outer Joins: Examples The following example shows how a partitioned outer join fills data gaps in rows to facilitate analytic function specification and reliable report formatting. The example first creates a small data table to be used in the join:

    SELECT d.department_id, e.last_name
       FROM departments d LEFT OUTER JOIN employees e
       ON d.department_id = e.department_id
       ORDER BY d.department_id, e.last_name;

Users familiar with the traditional Oracle Database outer joins syntax will recognize the same query in this form:

    SELECT d.department_id, e.last_name
       FROM departments d, employees e
       WHERE d.department_id = e.department_id(+)
       ORDER BY d.department_id, e.last_name;

Oracle strongly recommends that you use the more flexible `FROM` clause join syntax shown in the former example.

The left outer join returns all departments, including those without any employees. The same statement with a right outer join returns all employees, including those not yet assigned to a department:

Note: The employee Zeuss was added to the employees table for these examples, and is not part of the sample data.

    SELECT d.department_id, e.last_name
       FROM departments d RIGHT OUTER JOIN employees e
       ON d.department_id = e.department_id
       ORDER BY d.department_id, e.last_name;

    DEPARTMENT_ID LAST_NAME
    ------------- -------------------------
    . . .
              110 Higgins
              110 Gietz
                  Grant
                  Zeuss

It is not clear from this result whether employees Grant and Zeuss have `department_id` `NULL`, or whether their `department_id` is not in the `departments` table. To determine this requires a full outer join:

    SELECT d.department_id as d_dept_id, e.department_id as e_dept_id,
          e.last_name
       FROM departments d FULL OUTER JOIN employees e
       ON d.department_id = e.department_id
       ORDER BY d.department_id, e.last_name;

     D_DEPT_ID  E_DEPT_ID LAST_NAME
    ---------- ---------- -------------------------
      . . .
           110        110 Gietz
           110        110 Higgins
      . . .
           260
           270
                      999 Zeuss
                          Grant

Because the column names in this example are the same in both tables in the join, you can also use the common column feature by specifying the `USING` clause of the join syntax. The output is the same as for the preceding example except that the `USING` clause coalesces the two matching columns `department_id` into a single column output:

    SELECT department_id AS d_e_dept_id, e.last_name
       FROM departments d FULL OUTER JOIN employees e
       USING (department_id)
       ORDER BY department_id, e.last_name;

    D_E_DEPT_ID LAST_NAME
    ----------- -------------------------
      . . .
            110 Higgins
            110 Gietz
      . . .
            260
            270
            999 Zeuss
                Grant

Using Partitioned Outer Joins: Examples The following example shows how a partitioned outer join fills in gaps in rows to facilitate analytic calculation specification and reliable report formatting. The example first creates and populates a simple table to be used in the join:

    CREATE TABLE inventory (time_id    DATE,
                            product    VARCHAR2(10),
                            quantity   NUMBER);

    INSERT INTO inventory VALUES (TO_DATE('01/04/01', 'DD/MM/YY'), 'bottle', 10);
    INSERT INTO inventory VALUES (TO_DATE('06/04/01', 'DD/MM/YY'), 'bottle', 10);
    INSERT INTO inventory VALUES (TO_DATE('01/04/01', 'DD/MM/YY'), 'can', 10);
    INSERT INTO inventory VALUES (TO_DATE('04/04/01', 'DD/MM/YY'), 'can', 10);

    SELECT times.time_id, product, quantity FROM inventory
       PARTITION BY  (product)
       RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id)
       WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY')
          AND TO_DATE('06/04/01', 'DD/MM/YY')
       ORDER BY  2,1;

    TIME_ID   PRODUCT      QUANTITY
    --------- ---------- ----------
    01-APR-01 bottle             10
    02-APR-01 bottle
    03-APR-01 bottle
    04-APR-01 bottle
    05-APR-01 bottle
    06-APR-01 bottle             10
    06-APR-01 bottle              8
    01-APR-01 can                10
    01-APR-01 can                15
    02-APR-01 can
    03-APR-01 can
    04-APR-01 can                10
    04-APR-01 can                11
    05-APR-01 can
    06-APR-01 can

    15 rows selected.

The data is now more dense along the time dimension for each partition of the product dimension. However, each of the newly added rows within each partition is null in the quantity column. It is more useful to see the nulls replaced by the preceding non-`NULL` value in time order. You can achieve this by applying the analytic function `LAST_VALUE` on top of the query result:

    SELECT time_id, product, LAST_VALUE(quantity IGNORE NULLS)
       OVER (PARTITION BY product ORDER BY time_id) quantity
       FROM ( SELECT times.time_id, product, quantity
                 FROM inventory PARTITION BY  (product)
                    RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id)
       WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY')
          AND TO_DATE('06/04/01', 'DD/MM/YY'))
       ORDER BY  2,1;

    TIME_ID   PRODUCT      QUANTITY
    --------- ---------- ----------
    01-APR-01 bottle             10
    02-APR-01 bottle             10
    03-APR-01 bottle             10
    04-APR-01 bottle             10
    05-APR-01 bottle             10
    06-APR-01 bottle              8
    06-APR-01 bottle              8
    01-APR-01 can                15
    01-APR-01 can                15
    02-APR-01 can                15
    03-APR-01 can                15
    04-APR-01 can                11
    04-APR-01 can                11
    05-APR-01 can                11
    06-APR-01 can                11

    15 rows selected.

See Also: Oracle Data Warehousing Guide for an expanded discussion on filling gaps in time series calculations and examples of usage

Using Antijoins: Example The following example selects a list of employees who are not in a particular set of departments:

    SELECT * FROM employees
       WHERE department_id NOT IN
       (SELECT department_id FROM departments
           WHERE location_id = 1700)
       ORDER BY last_name;

Using Semijoins: Example In the following example, only one row needs to be returned from the `departments` table, even though many rows in the `employees` table might match the subquery. If no index has been defined on the `salary` column in `employees`, then a semijoin can be used to improve query performance.

    SELECT * FROM departments
       WHERE EXISTS
       (SELECT * FROM employees
           WHERE departments.department_id = employees.department_id
           AND employees.salary > 2500)
       ORDER BY department_name;

Table Collections: Examples You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the `query_table_expr_clause` of an `INSERT`, `DELETE`, or `UPDATE` statement is a `table_collection_expression`, the collection expression must be a subquery that uses the `TABLE` function to select the nested table column of the table. The examples that follow are based on the following scenario:

Suppose the database contains a table `hr_info` with columns `department_id`, `location_id`, and `manager_id`, and a column of nested table type `people` which has `last_name`, `department_id`, and `salary` columns for all the employees of each respective manager:

    CREATE TYPE people_typ AS OBJECT (
       last_name      VARCHAR2(25),
       department_id  NUMBER(4),
       salary         NUMBER(8,2));
    /
    CREATE TYPE people_tab_typ AS TABLE OF people_typ;
    /
    CREATE TABLE hr_info (
       department_id   NUMBER(4),
       location_id     NUMBER(4),
       manager_id      NUMBER(6),
       people          people_tab_typ)
       NESTED TABLE people STORE AS people_stor_tab;

    INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());

The following example inserts into the `people` nested table column of the `hr_info` table for department 280:

    INSERT INTO TABLE(SELECT h.people FROM hr_info h
       WHERE h.department_id = 280)
       VALUES ('Smith', 280, 1750);

The next example updates the department 280 `people` nested table:

    UPDATE TABLE(SELECT h.people FROM hr_info h
       WHERE h.department_id = 280) p
       SET p.salary = p.salary + 100;

The next example deletes from the department 280 `people` nested table:

    DELETE TABLE(SELECT h.people FROM hr_info h
       WHERE h.department_id = 280) p
       WHERE p.salary > 1700;

Collection Unnesting: Examples To select data from a nested table column, use the `TABLE` function to treat the nested table as columns of a table. This process is called collection unnesting.

You could get all the rows from `hr_info`, which was created in the preceding example, and all the rows from the `people` nested table column of `hr_info` using the following statement:

    SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(t1.people) t2
       WHERE t2.department_id = t1.department_id;

Now suppose that `people` is not a nested table column of `hr_info`, but is instead a separate table with columns `last_name`, `department_id`, `address`, `hiredate`, and `salary`. You can extract the same rows as in the preceding example with this statement:

    SELECT t1.department_id, t2.*
       FROM hr_info t1, TABLE(CAST(MULTISET(
          SELECT t3.last_name, t3.department_id, t3.salary
             FROM people t3
          WHERE t3.department_id = t1.department_id)
          AS people_tab_typ)) t2;

Finally, suppose that `people` is neither a nested table column of table `hr_info` nor a table itself. Instead, you have created a function `people_func` that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:

    SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST
       (people_func( ... ) AS people_tab_typ)) t2;

See Also: Oracle Database Application Developer's Guide - Fundamentals for more examples of collection unnesting.

Using the LEVEL Pseudocolumn: Examples The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is `AD_VP`. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.

    SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart,
            employee_id, manager_id, job_id
        FROM employees
        START WITH job_id = 'AD_VP'
        CONNECT BY PRIOR employee_id = manager_id;

    ORG_CHART          EMPLOYEE_ID MANAGER_ID JOB_ID
    ------------------ ----------- ---------- ----------
    Kochhar                    101        100 AD_VP
      Greenberg                108        101 FI_MGR
        Faviet                 109        108 FI_ACCOUNT
        Chen                   110        108 FI_ACCOUNT
        Sciarra                111        108 FI_ACCOUNT
        Urman                  112        108 FI_ACCOUNT
        Popp                   113        108 FI_ACCOUNT
      Whalen                   200        101 AD_ASST
      Mavris                   203        101 HR_REP
      Baer                     204        101 PR_REP
      Higgins                  205        101 AC_MGR
        Gietz                  206        205 AC_ACCOUNT
    De Haan                    102        100 AD_VP
      Hunold                   103        102 IT_PROG
        Ernst                  104        103 IT_PROG
        Austin                 105        103 IT_PROG
        Pataballa              106        103 IT_PROG
        Lorentz                107        103 IT_PROG

The following statement is similar to the previous one, except that it does not select employees with the job `FI_MAN`.

    SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart,
            employee_id, manager_id, job_id
        FROM employees
        WHERE job_id != 'FI_MGR'
        START WITH job_id = 'AD_VP'
        CONNECT BY PRIOR employee_id = manager_id;

    ORG_CHART          EMPLOYEE_ID MANAGER_ID JOB_ID
    ------------------ ----------- ---------- ----------
    Kochhar                    101        100 AD_VP
        Faviet                 109        108 FI_ACCOUNT
        Chen                   110        108 FI_ACCOUNT
        Sciarra                111        108 FI_ACCOUNT
        Urman                  112        108 FI_ACCOUNT
        Popp                   113        108 FI_ACCOUNT
      Whalen                   200        101 AD_ASST
      Mavris                   203        101 HR_REP
      Baer                     204        101 PR_REP
      Higgins                  205        101 AC_MGR
        Gietz                  206        205 AC_ACCOUNT
    De Haan                    102        100 AD_VP
      Hunold                   103        102 IT_PROG
        Ernst                  104        103 IT_PROG
        Austin                 105        103 IT_PROG
        Pataballa              106        103 IT_PROG
        Lorentz                107        103 IT_PROG

Oracle Database does not return the manager `Greenberg`, although it does return employees who are managed by `Greenberg`.

The following statement is similar to the first one, except that it uses the `LEVEL` pseudocolumn to select only the first two levels of the management hierarchy:

    SELECT LPAD(' ',2*(LEVEL-1)) || last_name org_chart,
    employee_id, manager_id, job_id
        FROM employees
        START WITH job_id = 'AD_PRES'
        CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 2;

    ORG_CHART          EMPLOYEE_ID MANAGER_ID JOB_ID
    ------------------ ----------- ---------- ----------
    King                       100            AD_PRES
      Kochhar                  101        100 AD_VP
      De Haan                  102        100 AD_VP
      Raphaely                 114        100 PU_MAN
      Weiss                    120        100 ST_MAN
      Fripp                    121        100 ST_MAN
      Kaufling                 122        100 ST_MAN
      Vollman                  123        100 ST_MAN
      Mourgos                  124        100 ST_MAN
      Russell                  145        100 SA_MAN
      Partners                 146        100 SA_MAN
      Errazuriz                147        100 SA_MAN
      Cambrault                148        100 SA_MAN
      Zlotkey                  149        100 SA_MAN
      Hartstein                201        100 MK_MAN

Using Distributed Queries: Example This example shows a query that joins the `departments` table on the local database with the `employees` table on the `remote` database:

    SELECT last_name, department_name
       FROM employees@remote, departments
       WHERE employees.department_id = departments.department_id;

Using Correlated Subqueries: Examples The following examples show the general syntax of a correlated subquery:

    SELECT select_list
        FROM table1 t_alias1
        WHERE expr operator
            (SELECT column_list
                FROM table2 t_alias2
                WHERE t_alias1.column
                   operator t_alias2.column);

    UPDATE table1 t_alias1
        SET column =
            (SELECT expr
                FROM table2 t_alias2
                WHERE t_alias1.column = t_alias2.column);

    DELETE FROM table1 t_alias1
        WHERE column operator
            (SELECT expr
                FROM table2 t_alias2
                WHERE t_alias1.column = t_alias2.column);

The following statement returns data about employees whose salaries exceed their department average. The following statement assigns an alias to `employees`, the table containing the salary information, and then uses the alias in a correlated subquery:

    SELECT department_id, last_name, salary
       FROM employees x
       WHERE salary > (SELECT AVG(salary)
          FROM employees
          WHERE x.department_id = department_id)
       ORDER BY department_id;

For each row of the `employees` table, the parent query uses the correlated subquery to compute the average salary for members of the same department. The correlated subquery performs the following steps for each row of the `employees` table:

1. The `department_id` of the row is determined.

2. The `department_id` is then used to evaluate the parent query.

3. If the salary in that row is greater than the average salary of the departments of that row, then the row is returned.

The subquery is evaluated once for each row of the `employees` table.

Selecting from the DUAL Table: Example The following statement returns the current date:

    SELECT SYSDATE FROM DUAL;

You could select `SYSDATE` from the `employees` table, but the database would return 14 rows of the same `SYSDATE`, one for every row of the `employees` table. Selecting from `DUAL` is more convenient.

Selecting Sequence Values: Examples The following statement increments the `employees_seq` sequence and returns the new value:

    SELECT employees_seq.nextval
        FROM DUAL;

The following statement selects the current value of `employees_seq`:

    SELECT employees_seq.currval
        FROM DUAL;